package com.eam.handler;

import com.basis.utils.BaseServletHandler;
import com.basis.utils.CodeGenerateRuleUtil;
import com.eam.data.*;
import com.mes.data.EmesProcess;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jettison.json.JSONObject;
import org.openbravo.dal.service.OBDal;
import org.openbravo.model.ad.access.User;
import org.openbravo.model.common.enterprise.Organization;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/*
 * 点检计划导入
 */
public class CheckPlanImportHandler  extends BaseServletHandler {
	
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//处理时间格式
	@Override
	protected JSONObject service(HttpServletRequest request, HttpServletResponse response) {
		JSONObject result = new JSONObject();
		try {
			result.put("desc", "系统繁忙，请稍后再试。");
			result.put("result", false);
			request.setCharacterEncoding("utf-8");
			DiskFileItemFactory factory = new DiskFileItemFactory();
			// 设置 缓存的大小，当上传文件的容量超过该缓存时，直接放到 暂时存储室
			factory.setSizeThreshold(1024 * 1024);
			ServletFileUpload upload = new ServletFileUpload(factory);
			// 可以上传多个文件
			List<FileItem> listItem = (List<FileItem>) upload.parseRequest(request);
			for (FileItem item : listItem) {
				if (!item.isFormField()) {
					String name = item.getName();
					if (item.getSize() > 3 * 1024 * 1024) {
						result.put("desc", "文件大小不能超过3M");
						return result;
					}
					InputStream is = item.getInputStream(); // 文件流
					try {
						Workbook wb = null;
						if (name.endsWith(".xls")) {
							wb = new HSSFWorkbook(is);
						} else if (name.endsWith(".xlsx")) {
							wb = new XSSFWorkbook(is);
						}
						if (wb != null) {
							result = this.readExcel(wb);
							if (!result.getBoolean("result")) {
								OBDal.getInstance().rollbackAndClose();
								return result;
							} else {
								OBDal.getInstance().commitAndClose();
								result.put("result", true);
								result.put("desc", "导入成功");
								return result;
							}
						} else {
							result.put("desc", "文件格式错误，至于许EXCEL的导入");
							return result;
						}
					} catch (Exception e) {
						OBDal.getInstance().rollbackAndClose();
						if (e.getClass().getName().contains("ConstraintViolationException")) {
							result.put("result", false);
							result.put("desc", "文件数据有问题，请仔细检查。");
						}
						e.printStackTrace();
					} finally {
						is.close();
					}
				}
			}
		} catch (Exception e) {
			OBDal.getInstance().rollbackAndClose();
			e.printStackTrace();
		}
		return result;
	}
	
	//2
	/**
	 * 读取excel记录
	 * 
	 * @param wb
	 * @return
	 * @throws Exception
	 */
	private JSONObject readExcel(Workbook wb) throws Exception {
		String meg = "", errorMsg;
		int number = wb.getNumberOfSheets();
		JSONObject result = new JSONObject();
		result.put("result", true);
		if (number <= 0) {
			result.put("result", false);
			result.put("desc", "文件格式错误。请仔细检查");
			return result;
		} else {
			// 读取第一页
			Sheet sheet = wb.getSheetAt(0);
			int lastRowNum = sheet.getLastRowNum();
			for (int i = 1; i <= lastRowNum; i++) {
				Row row = sheet.getRow(i);
				errorMsg = this.checkData(row);
				if (errorMsg != null && !"".equals(errorMsg)) {
					meg = errorMsg;
				}
				
			}
			if (!"".equals(meg)) {
				result.put("result", false);
				result.put("desc", meg);
			}
			return result;
			
		}
	}
	
	
	/**
	 * 开始存储记录
	 * @return
	 * @throws Exception
	 */
	
	private String checkData(Row row) throws Exception {
		String meg = "";
		String Personliable = readValue(row.getCell(1));// 计划创建人
		String days = readValue(row.getCell(2));		//点检周期
		String Remarkd= readValue(row.getCell(3));		//说明描述
		String reqipemt = readValue(row.getCell(4));	//点检设备编码
		String usershebei = readValue(row.getCell(5));  //点检设备责任人
		String checkproject = readValue(row.getCell(6)); //点检项目
		String Organizations = readValue(row.getCell(7)); //组织机构
		String status = "CREATE";						//状态为创建
		boolean yes = true;
		
		String sql = "select * from ad_user where name = ?";
		User counts = (User) OBDal.getInstance().getSession().createSQLQuery(sql).addEntity(User.ENTITY_NAME).setString(0, Personliable).uniqueResult();
		
		User usershebeis = (User) OBDal.getInstance().getSession().createSQLQuery(sql).addEntity(User.ENTITY_NAME).setString(0, usershebei).uniqueResult();
		
		
		String aql = "select * from Eam_Facilities where code = ? ";
		EamFacilities requipments = (EamFacilities) OBDal.getInstance().getSession().createSQLQuery(aql).addEntity(EamFacilities.ENTITY_NAME).setString(0, reqipemt).uniqueResult();
		
		String wql = " select count(*) from Eam_Check_Item where code = ? ";
		Long bianmas = (Long) OBDal.getInstance().getSession().createSQLQuery(wql).setString(0, checkproject).uniqueResult();
		
		String eql = "select * from Eam_Check_Item where code = ? ";
		EamCheckItem xiangmu = (EamCheckItem)OBDal.getInstance().getSession().createSQLQuery(eql).addEntity(EamCheckItem.ENTITY_NAME).setString(0, checkproject).uniqueResult();
		
		String rql ="select * from emes_process"
				+ " where emes_process_id = (select emes_process_id from emes_process_facilities  "
				+ " where Eam_Facilities_Id = (select Eam_Facilities_Id from Eam_Facilities  where code = ?)) ";
		
		
		EmesProcess processs = (EmesProcess)OBDal.getInstance().getSession().createSQLQuery(rql).addEntity(EmesProcess.ENTITY_NAME).setString(0, reqipemt).uniqueResult(); //查询设备对应的工序
		
		String uql = "select * from ad_org where name = ?";
		
		Organization orgIds  = (Organization)OBDal.getInstance().getSession().createSQLQuery(uql).addEntity(Organization.ENTITY_NAME).setString(0, Organizations).uniqueResult(); //查询组织机构的ID
		
		String  numbers =  "[1-9]{1}[0-9]{0,10}";
		Pattern patterns = Pattern.compile(numbers);
		
		Matcher matcherq = patterns.matcher(days);
		boolean as = matcherq.matches();
		
		
		
		if(counts != null){
			if(as){
				if(requipments != null){
					
					if(bianmas != 0){
						if(usershebeis != null){
							if(processs != null){
								
							
							
							EamCheckPlan checkplan = new EamCheckPlan();	//点检计划
							
							EamCheckPlanFacilities checkequipment = new EamCheckPlanFacilities();	//点检设备
							
							EamCheckPlanItem checkdetails = new EamCheckPlanItem(); //点检项目
							
							String code = CodeGenerateRuleUtil.getCode("Eam_Check_Plan", "code");
							
							//将当前系统时间加上点检周期得知下次点检时间
							int clycls=Integer.parseInt(days);
							Calendar ca = Calendar.getInstance();
							
							ca.add(Calendar.DATE, clycls);// days为增加的天数，可以改变的（点检周期）
							 
							Date d = new Date();
							 
							d = ca.getTime();
							 
							Date enddate = sdf.parse(sdf.format(d));  //增加了点检周期后的时间
							
							BigDecimal day=new BigDecimal(days);
							
							//存点检计划
							checkplan.setCode(code);
							checkplan.setDescription(Remarkd);
							checkplan.setCycle(day);
							checkplan.setOrganization(orgIds);
							checkplan.setNextCycleTime(enddate);
							checkplan.setState(status);
							checkplan.setUser(counts);
							OBDal.getInstance().save(checkplan);
							String idtime = checkplan.getId();
							OBDal.getInstance().flush();
							
							String pql ="select * from Eam_Check_Plan where Eam_Check_Plan_Id = ? ";
							
							EamCheckPlan EamFacilities = (EamCheckPlan)OBDal.getInstance().getSession().createSQLQuery(pql).addEntity(EamCheckPlan.ENTITY_NAME).setString(0, idtime).uniqueResult();
														
							
							checkequipment.setEamCheckPlan(EamFacilities); //存点检计划的ID
							
							checkequipment.setEamFacilities(requipments);
							checkequipment.setUser(usershebeis);
							checkequipment.setStatus(status); 
							checkequipment.setEmesProcess(processs);
							checkequipment.setActive(yes);
							OBDal.getInstance().save(checkequipment);
							String itemId = checkequipment.getId();
							OBDal.getInstance().flush();
							
							String qql ="select * from Eam_Check_Plan_Facilities where Eam_Check_Plan_Facilities_id = ? ";
							
							EamCheckPlanFacilities runitem = (EamCheckPlanFacilities)OBDal.getInstance().getSession().createSQLQuery(qql).addEntity(EamCheckPlanFacilities.ENTITY_NAME).setString(0, itemId).uniqueResult();
							
							//存点检项目
							checkdetails.setEamCheckPlanFacilities(runitem);	//存点检设备的ID
							checkdetails.setEamCheckItem(xiangmu);
							checkdetails.setStatus(status);
							OBDal.getInstance().save(checkdetails);
							}else{
								meg = "【第" + (row.getRowNum() + 1) + "行,点检设备没有对应的工序！！";
							}
						}else{
							meg = "【第" + (row.getRowNum() + 1) + "行,点检设备责任人不存在！！";
						}
						
					}else{
						meg = "【第" + (row.getRowNum() + 1) + "行,点检项目编码不存在！！";
					}
				}else{
					meg = "【第" + (row.getRowNum() + 1) + "行,点检设备编码不存在！！";
				}						
			}else{
				meg = "【第" + (row.getRowNum() + 1) + "行,点检周期格式不对！！";
			}
		}else{
			meg = "【第" + (row.getRowNum() + 1) + "行,计划创建人不存在！！";
		}
		return meg;
	}
	//}
	//3
	// CELL_TYPE_NUMERIC 数值型 0
	// CELL_TYPE_STRING 字符串型 1
	// CELL_TYPE_FORMULA 公式型 2
	// CELL_TYPE_BLANK 空值 3
	// CELL_TYPE_BOOLEAN 布尔型 4
	// CELL_TYPE_ERROR 错误 5
	private String readValue(Cell cell) throws Exception {
		if (cell == null) {
			return "";
		}
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return sdf.format(cell.getDateCellValue());
			}
			Double text = cell.getNumericCellValue();
			return String.valueOf(text.longValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue();
		} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
			throw new Exception("第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列，数据格式有问题,请检查");
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else {
			return "";
		}
	}
}